package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

/**
 * The Database access class which handles all access with the database.
 */
public class Database {

	private int cookiesPerPallet = 36 * 10 * 15;
	private int cookiesPerRecipe = 100;

	/**
	 * The database connection. Taken from lab 3
	 */
	private Connection conn;

	/**
	 * An SQL statement object. Taken from lab 3
	 */
	private Statement stmt;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later. Taken from lab 3
	 */
	public Database() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password. Taken from lab 3
	 * @param userName The user name.
	 * @param password The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database. Taken from lab 3
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established. Taken from
	 * lab 3
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	/**
	 * Gets the name of all cookies from the Cookie table in the database.
	 * @return String[] of all cookies in database.
	 */
	public String[] getCookieNames() {
		ArrayList<String> list = new ArrayList<String>();
		try {
			PreparedStatement stmt = conn
					.prepareStatement("Select cookieName from Cookie");
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				list.add(result.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list.toArray(new String[0]);
	}

	/**
	 * Creates a Pallet in the Database and make the changes which are needed.
	 * 
	 * @param cookieName is the name of the Cookie which the Pallet is to contain.
	 * @param date is the date of when the Pallet was produced.
	 * @return null if database exception, if there were any ingredients which
	 *         there wasn't enough of to create an Pallet a ArrayList<String> of
	 *         the names of said ingredients are returned, else an empty
	 *         ArrayList is returned.
	 */
	public ArrayList<String> producePallet(String cookieName, String date) {
		if(!isValidDate(date)){
			return null;
		}
		ArrayList<String> missingIngredients = new ArrayList<String>();
		try {
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn
					.prepareStatement("Select ingredientName, amountIngredient*? as amountNeeded, storedAmount "
							+ "from RecipeEntry natural join Ingredient "
							+ "where cookieName = ? for update");
			stmt.setDouble(1, cookiesPerPallet / cookiesPerRecipe);
			stmt.setString(2, cookieName);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				if (result.getDouble("storedAmount") <= result
						.getDouble("amountNeeded")) {
					missingIngredients.add(result.getString("ingredientName"));
				}
			}
			if (missingIngredients.isEmpty()) { // All ingredients are stored in
												// sufficient amounts.
				result.first();
				while (result.next()) { // Decrease the amount of ingredients in
										// storage
					changeIngredientStorage(result.getString("ingredientName"),
							-result.getDouble("amountNeeded"));
				}
				PreparedStatement makePallet = conn.prepareStatement(""
						+ "Insert into Pallet(cookieName, dateProduced) "
						+ "values (?, ?)"); // Create a Pallet:
				makePallet.setString(1, cookieName);
				makePallet.setString(2, date);
				makePallet.executeUpdate();
			}
			conn.commit();
		} catch (Exception e) {
			missingIngredients = null;
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return missingIngredients;
	}

	/**
	 * Changes the ingredient storage of a given ingredient.
	 * @param ingredientname is the name of the ingredient which is to be changed.
	 * @param amount is the amount it is to be changed by. 
	 * @throws SQLException if there is any SQL/database exceptions.
	 */
	private void changeIngredientStorage(String ingredientname, double amount)
			throws SQLException {
		PreparedStatement stmt;
		stmt = conn.prepareStatement("update Ingredient "
				+ "set storedAmount = storedAmount + ? "
				+ "where ingredientName = ?");
		stmt.setDouble(1, amount);
		stmt.setString(2, ingredientname);
		stmt.executeUpdate();
	}

	/**
	 * Returns all pallets between two given dates.
	 * @param first is from when the search starts.
	 * @param second is to when the search should contain.
	 * @return an ArrayList<Pallet> of all Pallets specified, null if exception.
	 */
	public ArrayList<Pallet> searchPallets(Date first, Date second) {
		return searchPallets(first, second, null);
	}

	/**
	 * Returns the Pallets of a given cookiename between two given dates.
	 * @param first is from when the search starts.
	 * @param second is to when the search should contain.
	 * @param cookieName is the name of the cookie the Pallet should contain.
	 * @return an ArrayList<Pallet> of all Pallets specified, null if exception.
	 */
	public ArrayList<Pallet> searchPallets(Date first, Date second,
			String cookieName) {
		ArrayList<Pallet> pallets = new ArrayList<Pallet>();
		PreparedStatement stmt;
		try {

			String str = "select * " + "from Pallet "
					+ "where ? <= dateProduced AND dateProduced <= ? ";
			if (cookieName == null) {
				stmt = conn.prepareStatement(str);
			} else {
				stmt = conn.prepareStatement(str + "AND cookieName = ?");
				stmt.setString(3, cookieName);
			}
			stmt.setDate(1, first);
			stmt.setDate(2, second);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				String id = result.getString("palletId");
				String orderId = result.getString("orderId");
				String cookieName1 = result.getString("cookieName");
				boolean isBlocked = result.getBoolean("isBlocked");
				Date produced = result.getDate("dateProduced");
				Date delivered = result.getDate("dateDelivered");

				Pallet pallet = new Pallet(id, orderId, cookieName1, isBlocked,
						produced, delivered);
				pallets.add(pallet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return pallets;
	}

	/**
	 * Blocks all Pallets between two given dates which contains a specified cookie.
	 * @param first is from when the search should start.
	 * @param second is to which date the search should continue with.
	 * @param cookieName is the cookie the Pallet should contains.
	 * @return true if it's successful, else false.
	 */
	public boolean blockPallets(Date first, Date second, String cookieName) {
		PreparedStatement stmt;
		try {
			stmt = conn
					.prepareStatement(""
							+ "update Pallet "
							+ "set isBlocked = true "
							+ "where ? < dateProduced AND dateProduced < ? AND cookieName = ?");
			stmt.setDate(1, first);
			stmt.setDate(2, second);
			stmt.setString(3, cookieName);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Static method to generate today's date.
	 * @return today's date as YYYY-MM-DD.
	 */
	public static String getDate() {
		GregorianCalendar gc = new GregorianCalendar();
		return getDate(gc.get(Calendar.YEAR), gc.get(Calendar.MONTH) + 1,
				gc.get(Calendar.DAY_OF_MONTH));
	}

	/**
	 * Makes X<10=>"0X" instead of X. Ex. 3 => 03 and 5 => 05.
	 * @param nbr the number which is to fit.
	 * @return a String of the number with a 0 before the number if it was less than 10.
	 */
	public static String fitDate(int nbr) {
		return "" + (nbr < 10 ? "0" + nbr : nbr);
	}

	/**
	 * Return the date as string.
	 * @param year is the year which is to be returned.
	 * @param month is the month which is to be returned.
	 * @param day is the day which is to be returned.
	 * @return the given date as a string specified as YYYY-MM-DD.
	 */
	public static String getDate(int year, int month, int day) {
		return year + "-" + fitDate(month) + "-" + fitDate(day);
	}
	
	/**
	 * Determines whether a string is a valid date.
	 * Copied from:
	 * http://www.javadb.com/check-if-a-string-is-a-valid-date
	 * @param inDate The date as a string in format yyyy-MM-dd.
	 * @return True if the date is valid. False otherwise.
	 */
	public boolean isValidDate(String inDate) {

	    if (inDate == null)
	      return false;

	    //set the format to use as a constructor argument
	    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	    
	    if (inDate.trim().length() != dateFormat.toPattern().length())
	      return false;

	    dateFormat.setLenient(false);
	    
	    try {
	      //parse the inDate parameter
	      dateFormat.parse(inDate.trim());
	    }
	    catch (ParseException pe) {
	      return false;
	    }
	    return true;
	  }

}
